package com.csii.autocode.utils;

import cn.hutool.core.util.ArrayUtil;
import com.csii.autocode.config.ReadConfig;
import com.csii.autocode.entity.ColumnVo;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import cn.hutool.core.util.StrUtil;
import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

import java.sql.PreparedStatement;
public class DbReadTableUtil {
    private static final Logger logger = LoggerFactory.getLogger(DbReadTableUtil.class);

    private static Connection connection;

    private static Statement statement;

    public static List<String> getTableName(String db,String dbName,String url,String userName,String password) throws SQLException {
        String str = null;
        String cs = null;
        String driverClass=null;
        ArrayList<String> arrayList = new ArrayList(0);
        try {
            switch (db.toLowerCase()) {
                case ("mysql"):
                    str = MessageFormat.format("select distinct table_name from information_schema.columns where table_schema = {0}", SqlUtil.stringSplice(dbName));
                    driverClass="com.mysql.cj.jdbc.Driver";
                    break;
                case("oracle"):
                    str = "select distinct colstable.table_name as  table_name from user_tab_cols colstable order by colstable.table_name";
                    driverClass="oracle.jdbc.driver.OracleDriver";
                    break;
                case("postgresql"):
                    str = "select tablename from pg_tables where schemaname='public'";
                    driverClass="org.postgresql.Driver";
                    break;
                case("sqlserver"):
                    str = "select distinct c.name as  table_name from sys.objects c where c.type = 'U' ";
                    driverClass="org.hibernate.dialect.SQLServerDialect";
                    break;
            }
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, userName, password);

            statement = connection.createStatement(1005, 1007);
            logger.debug("--------------sql-------------" + str);
            try(ResultSet resultSet= statement.executeQuery(str);) {
                while (resultSet.next()) {
                    String str1 = resultSet.getString(1);
                    arrayList.add(str1);
                }
            }
        } catch (Exception exception) {
            logger.error(exception.getMessage());
        } finally {
                if (statement != null) {
                    statement.close();
                    statement = null;
                }
                if (connection != null) {
                    connection.close();
                    connection = null;
                }
        }
        return arrayList;
    }

    public static List<ColumnVo> getTableColumn(String db,String url,String userName,String password,String tableName) throws Exception {
        String str = null;
        String driverClass=null;
        ArrayList<ColumnVo> arrayList1 = new ArrayList();
        try {

            switch (db.toLowerCase()) {
                case ("mysql"):
                    //str = MessageFormat.format("select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns where table_name = {0} and table_schema = {1} order by ORDINAL_POSITION", SqlUtil.stringSplice(tableName), SqlUtil.stringSplice(ReadConfig.getDatabaseName()));
                    str = "select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns where table_name = ? and table_schema = ? order by ORDINAL_POSITION";
                    driverClass="com.mysql.cj.jdbc.Driver";
                    break;
                case("oracle"):
                    //str = MessageFormat.format(" select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment, colstable.Data_Precision column_precision, colstable.Data_Scale column_scale,colstable.Char_Length,colstable.nullable from user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name  and colstable.table_name = {0}", SqlUtil.stringSplice(tableName.toUpperCase()));
                    str = "select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment, colstable.Data_Precision column_precision, colstable.Data_Scale column_scale,colstable.Char_Length,colstable.nullable from user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name  and colstable.table_name = ?";
                    driverClass="oracle.jdbc.driver.OracleDriver";
                    break;
                case("postgresql"):
                    //str = MessageFormat.format("select icm.column_name as field,icm.udt_name as type,fieldtxt.descript as comment, icm.numeric_precision_radix as column_precision ,icm.numeric_scale as column_scale ,icm.character_maximum_length as Char_Length,icm.is_nullable as attnotnull from information_schema.columns icm, (SELECT A.attnum,( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,A.attname FROM\tpg_catalog.pg_attribute A WHERE A.attrelid = ( SELECT oid FROM pg_class WHERE relname = {0} ) AND A.attnum > 0 AND NOT A.attisdropped  ORDER BY\tA.attnum ) fieldtxt where icm.table_name={1} and fieldtxt.attname = icm.column_name", SqlUtil.stringSplice(tableName), SqlUtil.stringSplice(tableName));
                    str = "select icm.column_name as field,icm.udt_name as type,fieldtxt.descript as comment, icm.numeric_precision_radix as column_precision ,icm.numeric_scale as column_scale ,icm.character_maximum_length as Char_Length,icm.is_nullable as attnotnull from information_schema.columns icm, (SELECT A.attnum,( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,A.attname FROM\tpg_catalog.pg_attribute A WHERE A.attrelid = ( SELECT oid FROM pg_class WHERE relname = ?) AND A.attnum > 0 AND NOT A.attisdropped  ORDER BY\tA.attnum ) fieldtxt where icm.table_name= ? and fieldtxt.attname = icm.column_name";
                    driverClass="org.postgresql.Driver";
                    break;
                case("sqlserver"):
                    //str = MessageFormat.format("select distinct cast(a.name as varchar(50)) column_name,  cast(connection.name as varchar(50)) data_type,  cast(e.value as NVARCHAR(200)) comment,  cast(ColumnProperty(a.object_id,a.Name,'Precision') as int) num_precision,  cast(ColumnProperty(a.object_id,a.Name,'Scale') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then 'y' else 'n' end) nullable,column_id   from sys.columns a left join sys.types connection on a.user_type_id=connection.user_type_id left join (select top 1 * from sys.objects where type = 'U' and name ={0}  order by name) c on a.object_id=c.object_id left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name={0} order by a.column_id", SqlUtil.stringSplice(tableName));
                    str  = "select distinct cast(a.name as varchar(50)) column_name,  cast(connection.name as varchar(50)) data_type,  cast(e.value as NVARCHAR(200)) comment,  cast(ColumnProperty(a.object_id,a.Name,'Precision') as int) num_precision,  cast(ColumnProperty(a.object_id,a.Name,'Scale') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then 'y' else 'n' end) nullable,column_id   from sys.columns a left join sys.types connection on a.user_type_id=connection.user_type_id left join (select top 1 * from sys.objects where type = 'U' and name = ?  order by name) c on a.object_id=c.object_id left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name= ? order by a.column_id";
                    driverClass="org.hibernate.dialect.SQLServerDialect";
                    break;
            }
            logger.debug("--------------sql-------------" + str);
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, userName, password);
            //statement = connection.createStatement(1005, 1007);
            PreparedStatement stmt = connection.prepareStatement(str);
            switch (db.toLowerCase()) {
                case ("mysql"):
                    stmt.setString(1,SqlUtil.stringSplice(tableName));
                    stmt.setString(2,SqlUtil.stringSplice(ReadConfig.getDatabaseName()));
                    break;
                case("oracle"):
                    stmt.setString(1,SqlUtil.stringSplice(tableName.toUpperCase()));
                    break;
                case("postgresql"):
                    stmt.setString(1,SqlUtil.stringSplice(tableName));
                    stmt.setString(2,SqlUtil.stringSplice(tableName));
                    break;
                case("sqlserver"):
                    stmt.setString(1,SqlUtil.stringSplice(tableName));
                    stmt.setString(2,SqlUtil.stringSplice(tableName));
                    break;

            }

            //try(ResultSet resultSet = statement.executeQuery(str)) {
            try(ResultSet resultSet = stmt.executeQuery()) {
                    resultSet.last();
                int j = resultSet.getRow();
                int k = j;
                if (k > 0) {
                    ColumnVo columnVo = new ColumnVo();
                    if (ReadConfig.getDbFiledConvert()) {
                        columnVo.setFieldName(toLowerCase(resultSet.getString(1).toLowerCase()));
                    } else {
                        columnVo.setFieldName(resultSet.getString(1).toLowerCase());
                    }
                    columnVo.setFieldDbName(resultSet.getString(1).toUpperCase());
                    columnVo.setFieldType(toLowerCase(resultSet.getString(2).toLowerCase()));
                    columnVo.setFieldDbType(toLowerCase(resultSet.getString(2).toLowerCase()));
                    columnVo.setPrecision(resultSet.getString(4));
                    columnVo.setScale(resultSet.getString(5));
                    columnVo.setCharmaxLength(resultSet.getString(6));
                    columnVo.setNullable(SqlUtil.isNullable(resultSet.getString(7)));
                    setType(columnVo);
                    columnVo.setFiledComment(StrUtil.isBlank(resultSet.getString(3)) ? columnVo.getFieldName() : resultSet.getString(3));
                    logger.debug("columnt.getFieldName() -------------" + columnVo.getFieldName());
                    String[] arrayOfString = new String[0];
                    if (ReadConfig.getPageFilterFields() != null)
                        arrayOfString = ReadConfig.getPageFilterFields().toLowerCase().split(",");
                    if (!ReadConfig.getDbTableId().equals(columnVo.getFieldName()) &&
                            !StrUtil.equalsAny(columnVo.getFieldDbName().toLowerCase(), arrayOfString))
                        arrayList1.add(columnVo);
                    while (resultSet.previous()) {
                        ColumnVo columnVo1 = new ColumnVo();
                        if (ReadConfig.getDbFiledConvert()) {
                            columnVo1.setFieldName(toLowerCase(resultSet.getString(1).toLowerCase()));
                        } else {
                            columnVo1.setFieldName(resultSet.getString(1).toLowerCase());
                        }
                        columnVo1.setFieldDbName(resultSet.getString(1).toUpperCase());
                        logger.debug("columnt.getFieldName() -------------" + columnVo1.getFieldName());
                        if (ReadConfig.getDbTableId().equals(columnVo1.getFieldName()) ||
                                StrUtil.equalsAny(columnVo1.getFieldDbName().toLowerCase(), arrayOfString))
                            continue;
                        columnVo1.setFieldType(toLowerCase(resultSet.getString(2).toLowerCase()));
                        columnVo1.setFieldDbType(toLowerCase(resultSet.getString(2).toLowerCase()));
                        logger.debug("-----po.setFieldType------------" + columnVo1.getFieldType());
                        columnVo1.setPrecision(resultSet.getString(4));
                        columnVo1.setScale(resultSet.getString(5));
                        columnVo1.setCharmaxLength(resultSet.getString(6));
                        columnVo1.setNullable(SqlUtil.isNullable(resultSet.getString(7)));
                        setType(columnVo1);
                        columnVo1.setFiledComment(StrUtil.isEmpty(resultSet.getString(3)) ? columnVo1.getFieldName() : resultSet.getString(3));
                        arrayList1.add(columnVo1);
                    }
                } else {
                    throw new Exception("该表不存在或者表中没有字段");
                }
                logger.debug("读取表成功");
            }catch (SQLException sqlException){
                logger.error(sqlException.getMessage());
            }
        } catch (ClassNotFoundException classNotFoundException) {
            throw classNotFoundException;
        } finally {
                if (statement != null) {
                    statement.close();
                    statement = null;
                }
                if (connection != null) {
                    connection.close();
                    connection = null;
                }
        }
        ArrayList<ColumnVo> arrayList2 = new ArrayList();
        for (int i = arrayList1.size() - 1; i >= 0; i--) {
            ColumnVo columnVo = arrayList1.get(i);
            arrayList2.add(columnVo);
        }
        return arrayList2;
    }

    public static List<ColumnVo> getVolumnVoList(String db,String url,String userName,String password,String dbName,String paramString) throws Exception {
        String str = null;
        String driverClass=null;
        ArrayList<ColumnVo> arrayList1 = new ArrayList();
        try {
            switch (db.toLowerCase()) {
                case ("mysql"):
                    //str = MessageFormat.format("select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns where table_name = {0} and table_schema = {1} order by ORDINAL_POSITION", SqlUtil.stringSplice(paramString), SqlUtil.stringSplice(dbName));
                    str = "select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns where table_name = ? and table_schema = ? order by ORDINAL_POSITION";
                    driverClass="com.mysql.cj.jdbc.Driver";
                    break;
                case("oracle"):
                    //str = MessageFormat.format(" select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment, colstable.Data_Precision column_precision, colstable.Data_Scale column_scale,colstable.Char_Length,colstable.nullable from user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name  and colstable.table_name = {0}", SqlUtil.stringSplice(paramString.toUpperCase()));
                    str = "select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment, colstable.Data_Precision column_precision, colstable.Data_Scale column_scale,colstable.Char_Length,colstable.nullable from user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name  and colstable.table_name = ?";
                    driverClass="oracle.jdbc.driver.OracleDriver";
                    break;
                case("postgresql"):
                    //str = MessageFormat.format("select icm.column_name as field,icm.udt_name as type,fieldtxt.descript as comment, icm.numeric_precision_radix as column_precision ,icm.numeric_scale as column_scale ,icm.character_maximum_length as Char_Length,icm.is_nullable as attnotnull from information_schema.columns icm, (SELECT A.attnum,( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,A.attname FROM\tpg_catalog.pg_attribute A WHERE A.attrelid = ( SELECT oid FROM pg_class WHERE relname = {0} ) AND A.attnum > 0 AND NOT A.attisdropped  ORDER BY\tA.attnum ) fieldtxt where icm.table_name={1} and fieldtxt.attname = icm.column_name", SqlUtil.stringSplice(paramString), SqlUtil.stringSplice(paramString));
                    str = "select icm.column_name as field,icm.udt_name as type,fieldtxt.descript as comment, icm.numeric_precision_radix as column_precision ,icm.numeric_scale as column_scale ,icm.character_maximum_length as Char_Length,icm.is_nullable as attnotnull from information_schema.columns icm, (SELECT A.attnum,( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,A.attname FROM\\tpg_catalog.pg_attribute A WHERE A.attrelid = ( SELECT oid FROM pg_class WHERE relname = ? ) AND A.attnum > 0 AND NOT A.attisdropped  ORDER BY\\tA.attnum ) fieldtxt where icm.table_name= ? and fieldtxt.attname = icm.column_name";
                    driverClass="org.postgresql.Driver";
                    break;
                case("sqlserver"):
                    //str = MessageFormat.format("select distinct cast(a.name as varchar(50)) column_name,  cast(connection.name as varchar(50)) data_type,  cast(e.value as NVARCHAR(200)) comment,  cast(ColumnProperty(a.object_id,a.Name,'Precision') as int) num_precision,  cast(ColumnProperty(a.object_id,a.Name,'Scale') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then 'y' else 'n' end) nullable,column_id   from sys.columns a left join sys.types connection on a.user_type_id=connection.user_type_id left join (select top 1 * from sys.objects where type = 'U' and name ={0}  order by name) c on a.object_id=c.object_id left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name={0} order by a.column_id", SqlUtil.stringSplice(paramString));
                    str = "select distinct cast(a.name as varchar(50)) column_name,  cast(connection.name as varchar(50)) data_type,  cast(e.value as NVARCHAR(200)) comment,  cast(ColumnProperty(a.object_id,a.Name,'Precision') as int) num_precision,  cast(ColumnProperty(a.object_id,a.Name,'Scale') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then 'y' else 'n' end) nullable,column_id   from sys.columns a left join sys.types connection on a.user_type_id=connection.user_type_id left join (select top 1 * from sys.objects where type = 'U' and name = ?  order by name) c on a.object_id=c.object_id left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name= ? order by a.column_id";
                    driverClass="org.hibernate.dialect.SQLServerDialect";
                    break;
            }
            logger.debug("--------------sql-------------" + str);
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, userName, password);
            PreparedStatement stmt = connection.prepareStatement(str);
            switch (db.toLowerCase()) {
                case ("mysql"):
                    stmt.setString(1,SqlUtil.stringSplice(paramString));
                    stmt.setString(2, SqlUtil.stringSplice(dbName));
                    break;
                case("oracle"):
                    stmt.setString(1,SqlUtil.stringSplice(paramString.toUpperCase()));
                    break;
                case("postgresql"):
                    stmt.setString(1,SqlUtil.stringSplice(paramString));
                    stmt.setString(2,SqlUtil.stringSplice(paramString));
                    break;
                case("sqlserver"):
                    stmt.setString(1,SqlUtil.stringSplice(paramString));
                    stmt.setString(2,SqlUtil.stringSplice(paramString));
                    break;
            }
            //statement = connection.createStatement(1005, 1007);
            //try(ResultSet resultSet = statement.executeQuery(str)) {
            try(ResultSet resultSet = stmt.executeQuery()) {
                resultSet.last();
                int j = resultSet.getRow();
                int k = j;
                if (k > 0) {
                    ColumnVo columnVo = new ColumnVo();
                    if (ReadConfig.getDbFiledConvert()) {
                        columnVo.setFieldName(toLowerCase(resultSet.getString(1).toLowerCase()));
                    } else {
                        columnVo.setFieldName(resultSet.getString(1).toLowerCase());
                    }
                    columnVo.setFieldDbName(resultSet.getString(1).toUpperCase());
                    columnVo.setPrecision(SqlUtil.isBlank(resultSet.getString(4)));
                    columnVo.setScale(SqlUtil.isBlank(resultSet.getString(5)));
                    columnVo.setCharmaxLength(SqlUtil.isBlank(resultSet.getString(6)));
                    columnVo.setNullable(SqlUtil.isNullable(resultSet.getString(7)));
                    columnVo.setFieldType(getFieldType(resultSet.getString(2).toLowerCase(), columnVo.getPrecision(), columnVo.getScale()));
                    columnVo.setFieldDbType(toLowerCase(resultSet.getString(2).toLowerCase()));
                    setType(columnVo);
                    columnVo.setFiledComment(StrUtil.isBlank(resultSet.getString(3)) ? columnVo.getFieldName() : resultSet.getString(3));
                    logger.debug("columnt.getFieldName() -------------" + columnVo.getFieldName());
                    arrayList1.add(columnVo);
                    while (resultSet.previous()) {
                        ColumnVo columnVo1 = new ColumnVo();
                        if (ReadConfig.getDbFiledConvert()) {
                            columnVo1.setFieldName(toLowerCase(resultSet.getString(1).toLowerCase()));
                        } else {
                            columnVo1.setFieldName(resultSet.getString(1).toLowerCase());
                        }
                        columnVo1.setFieldDbName(resultSet.getString(1).toUpperCase());
                        columnVo1.setPrecision(SqlUtil.isBlank(resultSet.getString(4)));
                        columnVo1.setScale(SqlUtil.isBlank(resultSet.getString(5)));
                        columnVo1.setCharmaxLength(SqlUtil.isBlank(resultSet.getString(6)));
                        columnVo1.setNullable(SqlUtil.isNullable(resultSet.getString(7)));
                        columnVo1.setFieldType(getFieldType(resultSet.getString(2).toLowerCase(), columnVo1.getPrecision(), columnVo1.getScale()));
                        columnVo1.setFieldDbType(toLowerCase(resultSet.getString(2).toLowerCase()));
                        setType(columnVo1);
                        columnVo1.setFiledComment(StrUtil.isBlank(resultSet.getString(3)) ? columnVo1.getFieldName() : resultSet.getString(3));
                        arrayList1.add(columnVo1);
                    }
                } else {
                    throw new Exception("该表不存在或者表中没有字段");
                }
                logger.debug("读取表成功");
            }catch (SQLException sqlException){
                logger.error(sqlException.getMessage());
            }
            } catch (ClassNotFoundException classNotFoundException) {
                logger.error(classNotFoundException.getMessage());
            } finally {
                    if (statement != null) {
                        statement.close();
                        statement = null;
                    }
                    if (connection != null) {
                        connection.close();
                        connection = null;
                    }
            }
        ArrayList<ColumnVo> arrayList2 = new ArrayList();
        for (int i = arrayList1.size() - 1; i >= 0; i--) {
            ColumnVo columnVo = arrayList1.get(i);
            arrayList2.add(columnVo);
        }
        return arrayList2;
    }

    //根据表名判断表是否存在
    public static boolean isExist(String db,String url,String userName,String password,String dbName,String tableName) {
        String str=null;
        String driverClass=null;
        ArrayList<ColumnVo> arrayList1 = new ArrayList();
        try {
            switch (db.toLowerCase()) {
                case ("mysql"):
                    //str = "select column_name,data_type,column_comment,0,0 from information_schema.columns where table_name = '" + tableName + "' and table_schema = '" + dbName + "'";
                    str = "select column_name,data_type,column_comment,0,0 from information_schema.columns where table_name = ? and table_schema = ?";
                    driverClass="com.mysql.cj.jdbc.Driver";
                    break;
                case("oracle"):
                    //str = "select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment from user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name  and colstable.table_name = '" + tableName.toUpperCase() + "'";
                    str = "select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment from user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name  and colstable.table_name = ?";
                    driverClass="oracle.jdbc.driver.OracleDriver";
                    break;
                case("postgresql"):
                    //str = MessageFormat.format("select icm.column_name as field,icm.udt_name as type,fieldtxt.descript as comment, icm.numeric_precision_radix as column_precision ,icm.numeric_scale as column_scale ,icm.character_maximum_length as Char_Length,icm.is_nullable as attnotnull from information_schema.columns icm, (SELECT A.attnum,( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,A.attname FROM\tpg_catalog.pg_attribute A WHERE A.attrelid = ( SELECT oid FROM pg_class WHERE relname = {0} ) AND A.attnum > 0 AND NOT A.attisdropped  ORDER BY\tA.attnum ) fieldtxt where icm.table_name={1} and fieldtxt.attname = icm.column_name", SqlUtil.stringSplice(tableName), SqlUtil.stringSplice(tableName));
                    str = "select icm.column_name as field,icm.udt_name as type,fieldtxt.descript as comment, icm.numeric_precision_radix as column_precision ,icm.numeric_scale as column_scale ,icm.character_maximum_length as Char_Length,icm.is_nullable as attnotnull from information_schema.columns icm, (SELECT A.attnum,( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,A.attname FROM\tpg_catalog.pg_attribute A WHERE A.attrelid = ( SELECT oid FROM pg_class WHERE relname = ? ) AND A.attnum > 0 AND NOT A.attisdropped  ORDER BY\tA.attnum ) fieldtxt where icm.table_name= ? and fieldtxt.attname = icm.column_name";
                    driverClass="org.postgresql.Driver";
                    break;
                case("sqlserver"):
                    //str = MessageFormat.format("select distinct cast(a.name as varchar(50)) column_name,  cast(connection.name as varchar(50)) data_type,  cast(e.value as NVARCHAR(200)) comment,  cast(ColumnProperty(a.object_id,a.Name,'Precision') as int) num_precision,  cast(ColumnProperty(a.object_id,a.Name,'Scale') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then 'y' else 'n' end) nullable,column_id   from sys.columns a left join sys.types connection on a.user_type_id=connection.user_type_id left join (select top 1 * from sys.objects where type = 'U' and name ={0}  order by name) c on a.object_id=c.object_id left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name={0} order by a.column_id", SqlUtil.stringSplice(tableName));
                    str = "select distinct cast(a.name as varchar(50)) column_name,  cast(connection.name as varchar(50)) data_type,  cast(e.value as NVARCHAR(200)) comment,  cast(ColumnProperty(a.object_id,a.Name,'Precision') as int) num_precision,  cast(ColumnProperty(a.object_id,a.Name,'Scale') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then 'y' else 'n' end) nullable,column_id   from sys.columns a left join sys.types connection on a.user_type_id=connection.user_type_id left join (select top 1 * from sys.objects where type = 'U' and name ={0}  order by name) c on a.object_id=c.object_id left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name= ? order by a.column_id";
                    driverClass="org.hibernate.dialect.SQLServerDialect";
                    break;
            }
            logger.debug("--------------sql-------------" + str);
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, userName, password);
            //代码扫描
            PreparedStatement stmt = connection.prepareStatement(str);
            switch (db.toLowerCase()) {
                case ("mysql"):
                    stmt.setString(1,SqlUtil.stringSplice(tableName));
                    stmt.setString(2, SqlUtil.stringSplice(dbName));
                    break;
                case("oracle"):
                    stmt.setString(1,SqlUtil.stringSplice(tableName.toUpperCase()));
                    break;
                case("postgresql"):
                    stmt.setString(1,SqlUtil.stringSplice(tableName));
                    stmt.setString(2,SqlUtil.stringSplice(tableName));
                    break;
                case("sqlserver"):
                    stmt.setString(1,SqlUtil.stringSplice(tableName));
                    stmt.setString(2,SqlUtil.stringSplice(tableName));
                    break;
            }
//            statement = connection.createStatement(1005, 1007);
//          try(ResultSet resultSet = statement.executeQuery(str)) {

            try(ResultSet resultSet = stmt.executeQuery()) {
            //statement = connection.createStatement(1005, 1007);
            //try(ResultSet resultSet = statement.executeQuery(str)) {
                resultSet.last();
                int i = resultSet.getRow();
                if (i > 0)
                    return true;
            } catch (SQLException sqlException){
                logger.error(sqlException.getMessage());
            }
        } catch (Exception exception) {
            logger.error(exception.getMessage());
            return false;
        }
        return false;
    }

    private static String toLowerCase(String paramString) {
        String[] arrayOfString = paramString.split("_");
        paramString = "";
        byte b;
        int i;
        for (b = 0, i = arrayOfString.length; b < i; b++) {
            if (b > 0) {
                String str = arrayOfString[b].toLowerCase();
                str = str.substring(0, 1).toUpperCase() + str.substring(1, str.length());
                paramString = paramString + str;
            } else {
                paramString = paramString + arrayOfString[b].toLowerCase();
            }
        }
        return paramString;
    }

    public static String toUpperCase(String paramString) {
        String[] arrayOfString = paramString.split("_");
        paramString = "";
        byte b;
        int i;
        for (b = 0, i = arrayOfString.length; b < i; b++) {
            if (b > 0) {
                String str = arrayOfString[b].toLowerCase();
                str = str.substring(0, 1).toUpperCase() + str.substring(1, str.length());
                paramString = paramString + str;
            } else {
                paramString = paramString + arrayOfString[b].toLowerCase();
            }
        }
        paramString = paramString.substring(0, 1).toUpperCase() + paramString.substring(1);
        return paramString;
    }

    private static void setType(ColumnVo paramColumnVo) {
        String str1 = paramColumnVo.getFieldType();
        String str2 = paramColumnVo.getScale();
        paramColumnVo.setClassType("inputxt");
        if ("N".equals(paramColumnVo.getNullable()))
            paramColumnVo.setOptionType("*");
        if ("datetime".equals(str1) || str1.contains("time")) {
            paramColumnVo.setClassType("easyui-datetimebox");
        } else if ("date".equals(str1)) {
            paramColumnVo.setClassType("easyui-datebox");
        } else if (str1.contains("int")) {
            paramColumnVo.setOptionType("n");
        } else if ("number".equals(str1)) {
            if (StrUtil.isNotBlank(str2) && Integer.parseInt(str2) > 0)
                paramColumnVo.setOptionType("d");
        } else if ("float".equals(str1) || "double".equals(str1) || "decimal".equals(str1)) {
            paramColumnVo.setOptionType("d");
        } else if ("numeric".equals(str1)) {
            paramColumnVo.setOptionType("d");
        }
    }

    private static String getFieldType(String paramString1, String paramString2, String paramString3) {
        if (paramString1.contains("char")) {
            paramString1 = "java.lang.String";
        } else if (paramString1.contains("int")) {
            paramString1 = "java.lang.Integer";
        } else if (paramString1.contains("float")) {
            paramString1 = "java.lang.Float";
        } else if (paramString1.contains("double")) {
            paramString1 = "java.lang.Double";
        } else if (paramString1.contains("number")) {
            if (StrUtil.isNotBlank(paramString3) && Integer.parseInt(paramString3) > 0) {
                paramString1 = "java.math.BigDecimal";
            } else if (StrUtil.isNotBlank(paramString2) && Integer.parseInt(paramString2) > 10) {
                paramString1 = "java.lang.Long";
            } else {
                paramString1 = "java.lang.Integer";
            }
        } else if (paramString1.contains("decimal")) {
            paramString1 = "java.math.BigDecimal";
        } else if (paramString1.contains("date")) {
            paramString1 = "java.util.Date";
        } else if (paramString1.contains("time")) {
            paramString1 = "java.util.Date";
        } else if (paramString1.contains("blob")) {
            paramString1 = "byte[]";
        } else if (paramString1.contains("clob")) {
            paramString1 = "java.sql.Clob";
        } else if (paramString1.contains("numeric")) {
            paramString1 = "java.math.BigDecimal";
        } else {
            paramString1 = "java.lang.Object";
        }
        return paramString1;
    }

    public static List<ColumnVo> getTableColumn(String tableName) throws Exception {
        return getTableColumn(ReadConfig.getDb(),ReadConfig.getUrl(),ReadConfig.getUserName(),ReadConfig.getPassword(),tableName);
    }

    public static List<ColumnVo> getVolumnVoList(String tableName) throws Exception {
        return getVolumnVoList(ReadConfig.getDb(),ReadConfig.getUrl(),ReadConfig.getUserName(),ReadConfig.getPassword(),ReadConfig.getDatabaseName(),tableName);
    }

    public static boolean isExist(String tableName) {
        return isExist(ReadConfig.getDb(),ReadConfig.getUrl(),ReadConfig.getUserName(),ReadConfig.getPassword(),ReadConfig.getDatabaseName(),tableName);
    }
}
